#-*- encoding: utf-8 -*-
import win32com.client 
from Misc.Utils import *

from DataAccess.QueryBlp import *

from datetime import date,timedelta

from ReportTool.HldReport import *
from ReportTool.PnLReport import *
from ReportTool.NAVReport import *
from ReportTool.MktValReport import *
from ReportTool.TradeReport import *
from ReportTool.RealizedPnL import *
from ReportTool.OnshoreHldReport import *

from Config.ReportConfig import *

def Monthly_Expo_And_Attr_Report(ref_date):

	tplt_file = TPLT_PATH + 'Monthly Exposure And Attribution.xls'
	xApp = win32com.client.Dispatch("Excel.Application")
	xBook = xApp.Workbooks.Open(tplt_file)
	
	row_sector = 3
	row_mktcap = 3
	row_liquid = 3
	
	d = date(2007, 10 ,31)
	while d <= ref_date:
	
		hld = query_hld(d, portf_list=['SC028'])
		pnl = query_pnl(date(d.year,d.month,1), d, portf_list=['SC028'])
		
		hld_dict = {} # hld_dict[(ticker, l/s)] = [amount, mkt_exposure]
		
		for elem in hld:
			hld_dict[(elem[0], elem[3])] = (elem[4], elem[12])
		
		blp_tick = []
		blp_eq_tick = []
		rcd = {}

		for elem in pnl:
			ticker = elem[0]		
			sec_type = elem[2]
			
			if sec_type == 'WARRANT' or ticker == 'CITINQ' or ticker == 'TCCTMQ':
				continue
			
			ls = elem[3]
			#ret = elem[6]
			ret = 1
			trd_curncy = elem[7]
			base_curncy = elem[8]
			amt = hld_dict[(ticker, ls)][0] if hld_dict.has_key((ticker, ls)) else 0
			exp = hld_dict[(ticker, ls)][1] if hld_dict.has_key((ticker, ls)) else 0
			
			#blp ticker
			blp_ticker = ticker
			if (sec_type == 'PNOTE') or (sec_type == 'STOCK' and trd_curncy == 'CNY'):
				blp_ticker = ch_ticker_to_blp_ticker(ticker)
			if (sec_type == 'STOCK' and trd_curncy == 'HKD'):
				blp_ticker = hk_ticker_to_blp_ticker(ticker)
			
			blp_tick.append(blp_ticker) 
			if is_equity(blp_ticker):
				blp_eq_tick.append(blp_ticker)
				
			rcd[blp_ticker] = [ls, amt, exp, ret, base_curncy]	
		
		sec_curncy_dict = query_blp_static(blp_tick, ['CRNCY'])
		sec_sector_dict = query_blp_static(blp_eq_tick, ['GICS_SECTOR_NAME'])
		mkt_cap_dict = query_blp_latest(blp_eq_tick, ['CUR_MKT_CAP'], d)
		volume_avg_3m_dict = query_blp_average(blp_eq_tick, ['PX_VOLUME'], d - timedelta(days=90), d)	
		
		sector_expo_dict  = {}
		mktcap_expo_dict = {}
		liquid_expo_dict  = {}
		sector_ret_dict  = {}
		mktcap_ret_dict  = {}
		liquid_ret_dict  = {}
		
		for tick in rcd.keys():
			ls = rcd[tick][0]
			amt = rcd[tick][1]
			exp = rcd[tick][2] 
			ret = rcd[tick][3]
			base_curncy = rcd[tick][4]
			local_curncy = sec_curncy_dict[tick]['CRNCY'] if sec_curncy_dict[tick]['CRNCY'] != 'NA' else 'CNY'
			fx = query_latest_fx(d, base_curncy, local_curncy) 
			
			if sec_sector_dict.has_key(tick) and sec_sector_dict[tick]['GICS_SECTOR_NAME'] != 'NA':
				gics_name = sec_sector_dict[tick]['GICS_SECTOR_NAME']
			else:
				gics_name = 'Others'
			
			if mkt_cap_dict.has_key(tick) and mkt_cap_dict[tick]['CUR_MKT_CAP'] != 'NA':
				mkt_cap_million_base = mkt_cap_dict[tick]['CUR_MKT_CAP']/fx
				mkt_cap_class = get_mkt_cap_class(mkt_cap_million_base, base_curncy)
			else:
				mkt_cap_class = 'Others'
				
			if volume_avg_3m_dict.has_key(tick) and volume_avg_3m_dict[tick]['PX_VOLUME'] != 'NA':
				volume_avg_3m = volume_avg_3m_dict[tick]['PX_VOLUME']
				liquid_class = get_liquid_class(amt, volume_avg_3m, 0.3)
			else:
				liquid_class = 'Others'
			
			if sector_expo_dict.has_key((gics_name, ls)):
				sector_expo_dict[(gics_name, ls)] += exp
			else:
				sector_expo_dict[(gics_name, ls)] = exp
				
			if mktcap_expo_dict.has_key((mkt_cap_class, ls)):
				mktcap_expo_dict[(mkt_cap_class, ls)] += exp
			else:
				mktcap_expo_dict[(mkt_cap_class, ls)] = exp

			if liquid_expo_dict.has_key((liquid_class, ls)):
				liquid_expo_dict[(liquid_class, ls)] += exp
			else:
				liquid_expo_dict[(liquid_class, ls)] = exp		

			if sector_ret_dict.has_key((gics_name, ls)):
				sector_ret_dict[(gics_name, ls)] += ret
			else:
				sector_ret_dict[(gics_name, ls)] = ret
				
			if mktcap_ret_dict.has_key((mkt_cap_class, ls)):
				mktcap_ret_dict[(mkt_cap_class, ls)] += ret
			else:
				mktcap_ret_dict[(mkt_cap_class, ls)] = ret

			if liquid_ret_dict.has_key((liquid_class, ls)):
				liquid_ret_dict[(liquid_class, ls)] += ret
			else:
				liquid_ret_dict[(liquid_class, ls)] = ret
		
		xSht = xBook.Worksheets('sector')
		xSht.Cells(row_sector, 1).Value = d.isoformat()
		#sector expo long
		xSht.Cells(row_sector, 2).Value = sector_expo_dict[('Consumer Discretionary', 'LONG')] if sector_expo_dict.has_key(('Consumer Discretionary', 'LONG')) else 0
		xSht.Cells(row_sector, 3).Value = sector_expo_dict[('Consumer Staples', 'LONG')] if sector_expo_dict.has_key(('Consumer Staples', 'LONG')) else 0
		xSht.Cells(row_sector, 4).Value = sector_expo_dict[('Energy', 'LONG')] if sector_expo_dict.has_key(('Energy', 'LONG')) else 0
		xSht.Cells(row_sector, 5).Value = sector_expo_dict[('Financials', 'LONG')] if sector_expo_dict.has_key(('Financials', 'LONG')) else 0
		xSht.Cells(row_sector, 6).Value = sector_expo_dict[('Industrials', 'LONG')] if sector_expo_dict.has_key(('Industrials', 'LONG')) else 0
		xSht.Cells(row_sector, 7).Value = sector_expo_dict[('Information Technology', 'LONG')] if sector_expo_dict.has_key(('Information Technology', 'LONG')) else 0			
		xSht.Cells(row_sector, 8).Value = sector_expo_dict[('Materials', 'LONG')] if sector_expo_dict.has_key(('Materials', 'LONG')) else 0
		xSht.Cells(row_sector, 9).Value = sector_expo_dict[('Telecommunication Services', 'LONG')] if sector_expo_dict.has_key(('Telecommunication Services', 'LONG')) else 0
		xSht.Cells(row_sector, 10).Value = sector_expo_dict[('Utilities', 'LONG')] if sector_expo_dict.has_key(('Utilities', 'LONG')) else 0			
		xSht.Cells(row_sector, 11).Value = sector_expo_dict[('Health Care', 'LONG')] if sector_expo_dict.has_key(('Health Care', 'LONG')) else 0
		xSht.Cells(row_sector, 12).Value = sector_expo_dict[('Others', 'LONG')] if sector_expo_dict.has_key(('Others', 'LONG')) else 0
		#sector expo short
		xSht.Cells(row_sector, 13).Value = sector_expo_dict[('Consumer Discretionary', 'SHORT')] if sector_expo_dict.has_key(('Consumer Discretionary', 'SHORT')) else 0
		xSht.Cells(row_sector, 14).Value = sector_expo_dict[('Consumer Staples', 'SHORT')] if sector_expo_dict.has_key(('Consumer Staples', 'SHORT')) else 0
		xSht.Cells(row_sector, 15).Value = sector_expo_dict[('Energy', 'SHORT')] if sector_expo_dict.has_key(('Energy', 'SHORT')) else 0
		xSht.Cells(row_sector, 16).Value = sector_expo_dict[('Financials', 'SHORT')] if sector_expo_dict.has_key(('Financials', 'SHORT')) else 0
		xSht.Cells(row_sector, 17).Value = sector_expo_dict[('Industrials', 'SHORT')] if sector_expo_dict.has_key(('Industrials', 'SHORT')) else 0
		xSht.Cells(row_sector, 18).Value = sector_expo_dict[('Information Technology', 'SHORT')] if sector_expo_dict.has_key(('Information Technology', 'SHORT')) else 0			
		xSht.Cells(row_sector, 19).Value = sector_expo_dict[('Materials', 'SHORT')] if sector_expo_dict.has_key(('Materials', 'SHORT')) else 0
		xSht.Cells(row_sector, 20).Value = sector_expo_dict[('Telecommunication Services', 'SHORT')] if sector_expo_dict.has_key(('Telecommunication Services', 'SHORT')) else 0
		xSht.Cells(row_sector, 21).Value = sector_expo_dict[('Utilities', 'SHORT')] if sector_expo_dict.has_key(('Utilities', 'SHORT')) else 0			
		xSht.Cells(row_sector, 22).Value = sector_expo_dict[('Health Care', 'SHORT')] if sector_expo_dict.has_key(('Health Care', 'SHORT')) else 0
		xSht.Cells(row_sector, 23).Value = sector_expo_dict[('Others', 'SHORT')] if sector_expo_dict.has_key(('Others', 'SHORT')) else 0		
		#sector atrr long
		xSht.Cells(row_sector, 24).Value = sector_ret_dict[('Consumer Discretionary', 'LONG')] if sector_ret_dict.has_key(('Consumer Discretionary', 'LONG')) else 0
		xSht.Cells(row_sector, 25).Value = sector_ret_dict[('Consumer Staples', 'LONG')] if sector_ret_dict.has_key(('Consumer Staples', 'LONG')) else 0
		xSht.Cells(row_sector, 26).Value = sector_ret_dict[('Energy', 'LONG')] if sector_ret_dict.has_key(('Energy', 'LONG')) else 0
		xSht.Cells(row_sector, 27).Value = sector_ret_dict[('Financials', 'LONG')] if sector_ret_dict.has_key(('Financials', 'LONG')) else 0
		xSht.Cells(row_sector, 28).Value = sector_ret_dict[('Industrials', 'LONG')] if sector_ret_dict.has_key(('Industrials', 'LONG')) else 0
		xSht.Cells(row_sector, 29).Value = sector_ret_dict[('Information Technology', 'LONG')] if sector_ret_dict.has_key(('Information Technology', 'LONG')) else 0			
		xSht.Cells(row_sector, 30).Value = sector_ret_dict[('Materials', 'LONG')] if sector_ret_dict.has_key(('Materials', 'LONG')) else 0
		xSht.Cells(row_sector, 31).Value = sector_ret_dict[('Telecommunication Services', 'LONG')] if sector_ret_dict.has_key(('Telecommunication Services', 'LONG')) else 0
		xSht.Cells(row_sector, 32).Value = sector_ret_dict[('Utilities', 'LONG')] if sector_ret_dict.has_key(('Utilities', 'LONG')) else 0			
		xSht.Cells(row_sector, 33).Value = sector_ret_dict[('Health Care', 'LONG')] if sector_ret_dict.has_key(('Health Care', 'LONG')) else 0
		xSht.Cells(row_sector, 34).Value = sector_ret_dict[('Others', 'LONG')] if sector_ret_dict.has_key(('Others', 'LONG')) else 0
		#sector atrr short
		xSht.Cells(row_sector, 35).Value = sector_ret_dict[('Consumer Discretionary', 'SHORT')] if sector_ret_dict.has_key(('Consumer Discretionary', 'SHORT')) else 0
		xSht.Cells(row_sector, 36).Value = sector_ret_dict[('Consumer Staples', 'SHORT')] if sector_ret_dict.has_key(('Consumer Staples', 'SHORT')) else 0
		xSht.Cells(row_sector, 37).Value = sector_ret_dict[('Energy', 'SHORT')] if sector_ret_dict.has_key(('Energy', 'SHORT')) else 0
		xSht.Cells(row_sector, 38).Value = sector_ret_dict[('Financials', 'SHORT')] if sector_ret_dict.has_key(('Financials', 'SHORT')) else 0
		xSht.Cells(row_sector, 39).Value = sector_ret_dict[('Industrials', 'SHORT')] if sector_ret_dict.has_key(('Industrials', 'SHORT')) else 0
		xSht.Cells(row_sector, 40).Value = sector_ret_dict[('Information Technology', 'SHORT')] if sector_ret_dict.has_key(('Information Technology', 'SHORT')) else 0			
		xSht.Cells(row_sector, 41).Value = sector_ret_dict[('Materials', 'SHORT')] if sector_ret_dict.has_key(('Materials', 'SHORT')) else 0
		xSht.Cells(row_sector, 42).Value = sector_ret_dict[('Telecommunication Services', 'SHORT')] if sector_ret_dict.has_key(('Telecommunication Services', 'SHORT')) else 0
		xSht.Cells(row_sector, 43).Value = sector_ret_dict[('Utilities', 'SHORT')] if sector_ret_dict.has_key(('Utilities', 'SHORT')) else 0			
		xSht.Cells(row_sector, 44).Value = sector_ret_dict[('Health Care', 'SHORT')] if sector_ret_dict.has_key(('Health Care', 'SHORT')) else 0
		xSht.Cells(row_sector, 45).Value = sector_ret_dict[('Others', 'SHORT')] if sector_ret_dict.has_key(('Others', 'SHORT')) else 0			
			
		#mkt cap expo long
		xSht = xBook.Worksheets('mktcap')
		xSht.Cells(row_mktcap, 1).Value = d.isoformat()
		xSht.Cells(row_mktcap, 2).Value = mktcap_expo_dict[('MktCap_A', 'LONG')] if mktcap_expo_dict.has_key(('MktCap_A', 'LONG')) else 0
		xSht.Cells(row_mktcap, 3).Value = mktcap_expo_dict[('MktCap_B', 'LONG')] if mktcap_expo_dict.has_key(('MktCap_B', 'LONG')) else 0
		xSht.Cells(row_mktcap, 4).Value = mktcap_expo_dict[('MktCap_C', 'LONG')] if mktcap_expo_dict.has_key(('MktCap_C', 'LONG')) else 0
		xSht.Cells(row_mktcap, 5).Value = mktcap_expo_dict[('MktCap_D', 'LONG')] if mktcap_expo_dict.has_key(('MktCap_D', 'LONG')) else 0
		xSht.Cells(row_mktcap, 6).Value = mktcap_expo_dict[('MktCap_E', 'LONG')] if mktcap_expo_dict.has_key(('MktCap_E', 'LONG')) else 0
		xSht.Cells(row_mktcap, 7).Value = mktcap_expo_dict[('Others', 'LONG')] if mktcap_expo_dict.has_key(('Others', 'LONG')) else 0
		#mkt cap expo short
		xSht.Cells(row_mktcap, 8).Value = mktcap_expo_dict[('MktCap_A', 'SHORT')] if mktcap_expo_dict.has_key(('MktCap_A', 'SHORT')) else 0
		xSht.Cells(row_mktcap, 9).Value = mktcap_expo_dict[('MktCap_B', 'SHORT')] if mktcap_expo_dict.has_key(('MktCap_B', 'SHORT')) else 0
		xSht.Cells(row_mktcap, 10).Value = mktcap_expo_dict[('MktCap_C', 'SHORT')] if mktcap_expo_dict.has_key(('MktCap_C', 'SHORT')) else 0
		xSht.Cells(row_mktcap, 11).Value = mktcap_expo_dict[('MktCap_D', 'SHORT')] if mktcap_expo_dict.has_key(('MktCap_D', 'SHORT')) else 0
		xSht.Cells(row_mktcap, 12).Value = mktcap_expo_dict[('MktCap_E', 'SHORT')] if mktcap_expo_dict.has_key(('MktCap_E', 'SHORT')) else 0
		xSht.Cells(row_mktcap, 13).Value = mktcap_expo_dict[('Others', 'SHORT')] if mktcap_expo_dict.has_key(('Others', 'SHORT')) else 0
		#mkt cap attr long
		xSht.Cells(row_mktcap, 14).Value = mktcap_ret_dict[('MktCap_A', 'LONG')] if mktcap_ret_dict.has_key(('MktCap_A', 'LONG')) else 0
		xSht.Cells(row_mktcap, 15).Value = mktcap_ret_dict[('MktCap_B', 'LONG')] if mktcap_ret_dict.has_key(('MktCap_B', 'LONG')) else 0
		xSht.Cells(row_mktcap, 16).Value = mktcap_ret_dict[('MktCap_C', 'LONG')] if mktcap_ret_dict.has_key(('MktCap_C', 'LONG')) else 0
		xSht.Cells(row_mktcap, 17).Value = mktcap_ret_dict[('MktCap_D', 'LONG')] if mktcap_ret_dict.has_key(('MktCap_D', 'LONG')) else 0
		xSht.Cells(row_mktcap, 18).Value = mktcap_ret_dict[('MktCap_E', 'LONG')] if mktcap_ret_dict.has_key(('MktCap_E', 'LONG')) else 0
		xSht.Cells(row_mktcap, 19).Value = mktcap_ret_dict[('Others', 'LONG')] if mktcap_ret_dict.has_key(('Others', 'LONG')) else 0
		#mkt cap attr short
		xSht.Cells(row_mktcap, 20).Value = mktcap_ret_dict[('MktCap_A', 'SHORT')] if mktcap_ret_dict.has_key(('MktCap_A', 'SHORT')) else 0
		xSht.Cells(row_mktcap, 21).Value = mktcap_ret_dict[('MktCap_B', 'SHORT')] if mktcap_ret_dict.has_key(('MktCap_B', 'SHORT')) else 0
		xSht.Cells(row_mktcap, 22).Value = mktcap_ret_dict[('MktCap_C', 'SHORT')] if mktcap_ret_dict.has_key(('MktCap_C', 'SHORT')) else 0
		xSht.Cells(row_mktcap, 23).Value = mktcap_ret_dict[('MktCap_D', 'SHORT')] if mktcap_ret_dict.has_key(('MktCap_D', 'SHORT')) else 0
		xSht.Cells(row_mktcap, 24).Value = mktcap_ret_dict[('MktCap_E', 'SHORT')] if mktcap_ret_dict.has_key(('MktCap_E', 'SHORT')) else 0
		xSht.Cells(row_mktcap, 25).Value = mktcap_ret_dict[('Others', 'SHORT')] if mktcap_ret_dict.has_key(('Others', 'SHORT')) else 0
			
		#liquid expo long
		xSht = xBook.Worksheets('liquid')
		xSht.Cells(row_liquid, 1).Value = d.isoformat()
		xSht.Cells(row_liquid, 2).Value = liquid_expo_dict[('Liquid_A', 'LONG')] if liquid_expo_dict.has_key(('Liquid_A', 'LONG')) else 0
		xSht.Cells(row_liquid, 3).Value = liquid_expo_dict[('Liquid_B', 'LONG')] if liquid_expo_dict.has_key(('Liquid_B', 'LONG')) else 0
		xSht.Cells(row_liquid, 4).Value = liquid_expo_dict[('Liquid_C', 'LONG')] if liquid_expo_dict.has_key(('Liquid_C', 'LONG')) else 0
		xSht.Cells(row_liquid, 5).Value = liquid_expo_dict[('Liquid_D', 'LONG')] if liquid_expo_dict.has_key(('Liquid_D', 'LONG')) else 0
		xSht.Cells(row_liquid, 6).Value = liquid_expo_dict[('Others', 'LONG')] if liquid_expo_dict.has_key(('Others', 'LONG')) else 0
		#liquid expo short
		xSht.Cells(row_liquid, 7).Value = liquid_expo_dict[('Liquid_A', 'SHORT')] if liquid_expo_dict.has_key(('Liquid_A', 'SHORT')) else 0
		xSht.Cells(row_liquid, 8).Value = liquid_expo_dict[('Liquid_B', 'SHORT')] if liquid_expo_dict.has_key(('Liquid_B', 'SHORT')) else 0
		xSht.Cells(row_liquid, 9).Value = liquid_expo_dict[('Liquid_C', 'SHORT')] if liquid_expo_dict.has_key(('Liquid_C', 'SHORT')) else 0
		xSht.Cells(row_liquid, 10).Value = liquid_expo_dict[('Liquid_D', 'SHORT')] if liquid_expo_dict.has_key(('Liquid_D', 'SHORT')) else 0
		xSht.Cells(row_liquid, 11).Value = liquid_expo_dict[('Others', 'SHORT')] if liquid_expo_dict.has_key(('Others', 'SHORT')) else 0
		#liquid atrr long
		xSht.Cells(row_liquid, 12).Value = liquid_ret_dict[('Liquid_A', 'LONG')] if liquid_ret_dict.has_key(('Liquid_A', 'LONG')) else 0
		xSht.Cells(row_liquid, 13).Value = liquid_ret_dict[('Liquid_B', 'LONG')] if liquid_ret_dict.has_key(('Liquid_B', 'LONG')) else 0
		xSht.Cells(row_liquid, 14).Value = liquid_ret_dict[('Liquid_C', 'LONG')] if liquid_ret_dict.has_key(('Liquid_C', 'LONG')) else 0
		xSht.Cells(row_liquid, 15).Value = liquid_ret_dict[('Liquid_D', 'LONG')] if liquid_ret_dict.has_key(('Liquid_D', 'LONG')) else 0
		xSht.Cells(row_liquid, 16).Value = liquid_ret_dict[('Others', 'LONG')] if liquid_ret_dict.has_key(('Others', 'LONG')) else 0
		#liquid atrr short
		xSht.Cells(row_liquid, 17).Value = liquid_ret_dict[('Liquid_A', 'SHORT')] if liquid_ret_dict.has_key(('Liquid_A', 'SHORT')) else 0
		xSht.Cells(row_liquid, 18).Value = liquid_ret_dict[('Liquid_B', 'SHORT')] if liquid_ret_dict.has_key(('Liquid_B', 'SHORT')) else 0
		xSht.Cells(row_liquid, 19).Value = liquid_ret_dict[('Liquid_C', 'SHORT')] if liquid_ret_dict.has_key(('Liquid_C', 'SHORT')) else 0
		xSht.Cells(row_liquid, 20).Value = liquid_ret_dict[('Liquid_D', 'SHORT')] if liquid_ret_dict.has_key(('Liquid_D', 'SHORT')) else 0
		xSht.Cells(row_liquid, 21).Value = liquid_ret_dict[('Others', 'SHORT')] if liquid_ret_dict.has_key(('Others', 'SHORT')) else 0
			
		print '   running info, exposure and attribution completed at ', d.isoformat()
		d = end_of_mth(d, 1)
		row_sector += 1
		row_mktcap += 1
		row_liquid += 1
			
	rept_file = REPT_PATH + 'Monthly Exposure And Attribution.xls'
	xBook.SaveAs(rept_file)
	xBook.Close()
	del xApp
	
	print 'info, monthly exposure and attribution report completed'	
	